<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>Creating, altering and dropping tables</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="MySQL, tutorial, tables, DDL, data definition language, CREATE, DROP, ALTER">
<meta name="description" content="In this part of the MySQL tutorial, we will be
creating, altering and dropping tables.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

</head>

<body>

<div class="container">

<div id="wide_ad" class="ltow">
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<div class="content">


<a href="/" title="Home">Home</a>&nbsp;
<a href="..">Contents</a>


<h1>Creating, altering and dropping tables</h1>


<p>
In this part of the MySQL tutorial, we will be creating, altering and dropping
tables. 
</p>

<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* NewSquare */
google_ad_slot = "0364418177";
google_ad_width = 300;
google_ad_height = 250;
//-->
</script> 
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> 
</script>

<p>
We will use the following SQL statements:
</p>

<ul>
  <li>CREATE TABLE</li>
  <li>ALTER TABLE</li>
  <li>DROP TABLE</li>
</ul>

<p>
The CREATE, ALTER and DROP statements are not limited to tables. We can use them
to create other database objects like events, triggers, views, functions or
procedures. These statements are part of the Data Definition Language (DDL) 
of the SQL specification.
</p>


<h2 id="create">CREATE and DROP statements</h2>


<p>
The CREATE statement is used to create tables. It is also used to create 
indexes, views, events, routines and triggers.
</p>

<p>
To create a table, we give a name to a table and to its columns. Each column has a data type.
We have covered various MySQL data types in the previous chapter. Choosing the correct
datatype for the columns is part of the initial design of the database. 
</p>

<pre class="code">
mysql> CREATE TABLE Testing(Id INTEGER);
</pre>

<p>
We create a simple Testing table with the <code>CREATE TABLE</code> statement. 
The table name is Testing. The table has one column called Id. And column's
datatype is <code>INTEGER</code>.
</p>

<pre class="code">
mysql> SHOW CREATE TABLE Testing;
+---------+------------------------------------------
| Table   | Create Table                                                                                
+---------+------------------------------------------
| Testing | CREATE TABLE `Testing` (
  `Id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+------------------------------------------
1 row in set (0.00 sec)
</pre>

<p>
Using the <code>SHOW CREATE TABLE</code> statement, we can see the exact SQL statement
to create a specific table. There are also some defaults. These are chosen if
we do not provide some explicit attributes. The default MySQL engine is InnoDB if
not specified otherwise. (This applies to MySQL 5.5+). The default charset it latin1.
</p>

<pre class="code">
mysql> SHOW TABLES LIKE 'T%';
+---------------------+
| Tables_in_mydb (T%) |
+---------------------+
| Testing             |
+---------------------+
</pre>

<p>
Created, non-temporary tables can be shown using the <code>SHOW TABLES</code>
syntax. We can restrict the tables shown using the <code>LIKE</code> clause.
In our case, we show all tables that begin with T.
</p>

<pre class="code">
mysql> DROP TABLE Testing;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW TABLES LIKE 'T%';
Empty set (0.00 sec)
</pre>

<p>
The <code>DROP TABLE</code> statement drops a table from the database. 
</p>

<pre class="code">
mysql> CREATE TABLE Testing(Id INT NOT NULL) ENGINE=MEMORY CHARACTER SET='utf8'
    -> COLLATE='utf8_slovak_ci';
</pre>

<p>
We recreate the Testing table. The <code>INT</code> is a synonym for <code>INTEGER</code>.
The database engine is explicitly set to <code>MEMORY</code>. We also specify the 
character set and collation. 
</p>



<h2 id="alter">ALTER TABLE statement</h2>

<p>
The <code>ALTER TABLE</code> statement changes the structure of an existing table. It
is possible to add a new column, delete a column, rename column and table or change
the type of the table. In the following examples, we will demonstrate some of the
possibilities.
</p>

<pre class="code">
mysql> ALTER TABLE Testing RENAME TO TestTable;

mysql> SHOW TABLES LIKE 'T%';
+---------------------+
| Tables_in_mydb (T%) |
+---------------------+
| TestTable           |
+---------------------+
</pre>

<p>
We use the <code>RENAME TO</code> clause to rename the 
Testing table to TestTable.
</p>

<pre class="code">
mysql> ALTER TABLE TestTable ADD iValues INT;
</pre>

<p>
We add a new column named iValues to the table. 
</p>

<pre class="code">
mysql> SHOW COLUMNS FROM TestTable;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| Id      | int(11) | NO   |     | NULL    |       |
| iValues | int(11) | YES  |     | NULL    |       |
+---------+---------+------+-----+---------+-------+
</pre>

<p>
The statement shows available columns in the table. We see the newly 
added column. 
</p>

<p>
It is possible to add constraints to the table.
</p>

<pre class="code">
mysql> ALTER TABLE TestTable ADD PRIMARY KEY (Id);
</pre>

<p>
We add a <code>PRIMARY KEY</code> constraint to the TestTable.
</p>

<pre class="code">
mysql> DESCRIBE TestTable;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| Id      | int(11) | NO   | PRI | NULL    |       |
| iValues | int(11) | YES  |     | NULL    |       |
+---------+---------+------+-----+---------+-------+
</pre>

<p>
The <code>DESCRIBE</code> is a synonym for <code>SHOW COLUMNS FROM</code>.
We can see under the Key column that the primary key constraint is set 
for the Id column. 
</p>

<pre class="code">
mysql> ALTER TABLE TestTable CHANGE COLUMN iValues iValues1 INT;
</pre>

<p>
In this SQL statement we change the column name from iValues to iValues1.
</p>

<pre class="code">
mysql> ALTER TABLE TestTable MODIFY COLUMN iValues1 MEDIUMINT;

mysql> DESCRIBE TestTable;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| Id       | int(11)      | NO   | PRI | NULL    |       |
| iValues1 | mediumint(9) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
</pre>

<p>
We use the above SQL statement to modify the column definition. 
We change the column datatype from <code>INTEGER</code> to 
<code>MEDIUMINTEGER</code>.
</p>

<pre class="code">
mysql> ALTER TABLE TestTable DROP COLUMN iValues1;

mysql> DESCRIBE TestTable;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| Id    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
</pre>

<p>
In our last example, we drop an existing column from 
the table. 
</p>


<p>
In this part of the MySQL tutorial, we were creating, altering and dropping
tables. 
</p>


<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="..">Contents</a></span> ‡
<span class="botNavItem"><a href="#">Top of Page</a></span>
</div>

<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified February 1, 2011  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>
